Excel BI - Excel Challenge 897

excel-challenges

excel-formulas

🔰 Calculate the Total Special Score for all numbers within the range defined by Range Start and Range End (inclusive).

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 897

Challenge Description

🔰 Calculate the Total Special Score for all numbers within the range defined by Range Start and Range End (inclusive).

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/800-899/897/897 Special Score.xlsx"
input <- read_excel(path, range = "A1:B10")
test <- read_excel(path, range = "C1:C10")

calc_score = function(x) {
  x = as.character(x)
  n = nchar(x)
  if (n < 3) {
    return(0)
  }
  scores = purrr::map_int(1:(n - 2), function(i) {
    triplet = substr(x, i, i + 2)
    digits = as.integer(strsplit(triplet, "")[[1]])
    peak = as.integer(digits[1] > digits[2] && digits[3] > digits[2])
    valley = 2L * as.integer(digits[1] < digits[2] && digits[3] < digits[2])
    return(peak + valley)
  })
  return(sum(scores))
}

result = input %>%
  rowwise() %>%
  mutate(seq = list(seq(`Range Start`, `Range End`, by = 1))) %>%
  mutate(`Answer Expected` = sum(sapply(seq, calc_score))) %>%
  select(`Answer Expected`) %>%
  ungroup()

all_equal(result, test)
# [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns.
Strengths: The solution stays close to the text pattern itself, which makes the extraction logic easy to audit.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: A small number of well-targeted text patterns does most of the heavy lifting.

import pandas as pd

path = "Excel/800-899/897/897 Special Score.xlsx"
input_df = pd.read_excel(path, usecols="A:B", nrows=10)
test_df = pd.read_excel(path, usecols="C", nrows=10)

def calc_score(x):
    x = str(x)
    if len(x) < 3:
        return 0
    return sum(
        int(x[i] > x[i+1] and x[i+2] > x[i+1]) +
        2 * int(x[i] < x[i+1] and x[i+2] < x[i+1])
        for i in range(len(x) - 2)
    )

def seq_range(start, end):
    return list(range(int(start), int(end) + 1))

result = input_df.apply(lambda row: sum(calc_score(x) for x in seq_range(row['Range Start'], row['Range End'])), axis=1)
result_df = pd.DataFrame({'Answer Expected': result})

print(result_df.equals(test_df))
# True

The Python version expresses the core extraction rule directly and keeps the pattern matching easy to review.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.